﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using ShopB2C.Entity;
using Valor.DBUtility.MSSQLHelper;

namespace ShopB2C.SQLDataAccess
{
    public class UserAccess 
    {
        #region 存储过程
        /// <summary>
        /// 注册新用户
        /// </summary>
        private const string _regNewUser = "User_RegNewUser";
        /// <summary>
        /// 地址簿列表
        /// </summary>
        private const string _addressBookList = "User_Address_Select";
        /// <summary>
        /// 更新地址簿
        /// </summary>
        private const string _addressUpdate = "User_Address_Update";
        /// <summary>
        /// 删除地址簿
        /// </summary>
        private const string _addressDelete = "User_Address_Delete";
        /// <summary>
        /// 更新注册信息
        /// </summary>
        private const string _update = "User_Update";
        /// <summary>
        /// 修改密码
        /// </summary>
        private const string _changePassword = "User_ChangePassword";
        /// <summary>
        /// 用户登录
        /// </summary>
        private const string _login = "User_Login";
        /// <summary>
        /// 会员列表
        /// </summary>
        private const string _selectList = "User_SelectList";
        private const string _profile = "User_UserProfile";
        private const string _chageState = "User_ChangeUserState";
        #endregion

        public UserAccess(string connectionstring)
        {
            SQLProcedureHelper.ConnectionString = connectionstring;
        }
        /// <summary>
        /// 获取用户地址簿
        /// </summary>
        /// <param name="uid">关联的用户ID</param>
        /// <returns>用户地址簿</returns>
        public List<UserAddressEntity> AddressBookList(int uid)
        {
            List<UserAddressEntity> addressBook = new List<UserAddressEntity>();
            SqlParameter[] parameters = 
          {
              new SqlParameter("@Uid",SqlDbType.Int)
          };
            parameters[0].Value = uid;
            SqlDataReader reader = SQLProcedureHelper.ExecuteReader(_addressBookList, parameters);
            while (reader.Read())
            {
                addressBook.Add(new UserAddressEntity()
                {
                    AddressId = Convert.ToInt32(reader["AddressId"]) ,
                    ReallyName = reader["ReallyName"].ToString() ,
                    Address = reader["Address"].ToString() ,
                    Description = reader["Description"].ToString() ,
                    Mobile = reader["Mobile"].ToString() ,
                    Phone = reader["Phone"].ToString() ,
                    PostNo = reader["PostNo"].ToString()
                });
            }
            reader.Close();
            return addressBook;
        }
        /// <summary>
        /// 保存地址簿
        /// </summary>
        /// <param name="user">关联的用户对象</param>
        /// <returns>当前被操作的地址ID,如果是新增地址则返回该地址的自增ID，如果是修改则是当前被操作的地址ID</returns>
        public int SaveAddressBook(UserAddressEntity user)
        {
            int result = 0;
            SqlParameter[] parameters = 
            {
                new SqlParameter("@ReallyName",SqlDbType.NVarChar,50)
                ,new SqlParameter("@Address",SqlDbType.NVarChar,50)
                ,new SqlParameter("@Mobile",SqlDbType.NVarChar,50)
                ,new SqlParameter("@Phone",SqlDbType.NVarChar,50)
                ,new SqlParameter("@PostNo",SqlDbType.NVarChar,50)
                ,new SqlParameter("@Description",SqlDbType.NVarChar,50)
                ,new SqlParameter("@Uid",SqlDbType.Int)
                ,new SqlParameter("@AddressId",SqlDbType.Int)
            };
            parameters[0].Value = user.ReallyName;
            parameters[1].Value = user.Address;
            parameters[2].Value = user.Mobile;
            parameters[3].Value = user.Phone;
            parameters[4].Value = user.PostNo;
            parameters[5].Value = user.Description;
            parameters[6].Value = user.Uid;
            parameters[7].Value = user.AddressId;

            result = Convert.ToInt32(SQLProcedureHelper.ExecuteScalar(_addressUpdate, parameters));
            return result;
        }
        /// <summary>
        /// 删除收货人地址
        /// </summary>
        /// <param name="addressId">地址簿ID</param>
        /// <param name="uid">关联的用户ID</param>
        /// <returns>受影响的行</returns>
        public int DeleteAddressBook(int addressId , int uid)
        {
            int result = 0;
            SqlParameter[] parameters = 
            {
                new SqlParameter("@Uid",SqlDbType.Int)
                , new SqlParameter("@AddressId",SqlDbType.Int)
            };
            parameters[0].Value = uid;
            parameters[1].Value = addressId;

            result = SQLProcedureHelper.ExecuteNonQuery(_addressDelete, parameters);
            return result;

        }
        /// <summary>
        /// 更新注册信息
        /// </summary>
        /// <param name="userInfo">包含用户信息的实体类</param>
        /// <returns>受影响的行</returns>
        public int Update(UserEntity userInfo)
        {
            int result = 0;
            SqlParameter[] parameters = 
            {
                new SqlParameter("@Uid",SqlDbType.Int)
                ,new SqlParameter("@NickName",SqlDbType.NVarChar,50)
                ,new SqlParameter("@MobilePhone",SqlDbType.NVarChar,50)
                ,new SqlParameter("@Phone",SqlDbType.NVarChar,50)
                ,new SqlParameter("@QQ",SqlDbType.NVarChar,50)
                ,new SqlParameter("@Ali",SqlDbType.NVarChar,50)
                ,new SqlParameter("@MSN",SqlDbType.NVarChar,50)
                ,new SqlParameter("@Face",SqlDbType.NVarChar,50){Value = userInfo.Face}
            };

            parameters[0].Value = userInfo.Uid;
            parameters[1].Value = userInfo.NickName;
            parameters[2].Value = userInfo.MobilePhone;
            parameters[3].Value = userInfo.Phone;
            parameters[4].Value = userInfo.QQ;
            parameters[5].Value = userInfo.Ali;
            parameters[5].Value = userInfo.Msn;

            result = SQLProcedureHelper.ExecuteNonQuery(_update, parameters);

            return result;
        }
        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="uid">关联的用户ID</param>
        /// <param name="oldPassword">旧密码</param>
        /// <param name="password">新密码</param>
        public int ChangePassword(int uid , string oldPassword , string password)
        {
            int result = 0;
            SqlParameter[] parameters = 
            {
                new SqlParameter("@Uid",SqlDbType.Int)
                ,new SqlParameter("@Password",SqlDbType.NVarChar,50)
                ,new SqlParameter("@OldPassword",SqlDbType.NVarChar,50)
            };

            parameters[0].Value = uid;
            parameters[1].Value = password;
            parameters[2].Value = oldPassword;

            result = Convert.ToInt32(SQLProcedureHelper.ExecuteScalar(_changePassword, parameters));
            return result;
        }
        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="uname">用户名</param>
        /// <param name="password">密码</param>
        /// <returns>登陆后的用户资料</returns>
        public UserEntity Login(string uname , string password)
        {
            UserEntity user = null;
            SqlParameter[] parameters = 
            {
                new  SqlParameter("@Uname",SqlDbType.NVarChar,50)
                ,new SqlParameter("@Password",SqlDbType.NVarChar,50)
            };
            parameters[0].Value = uname;
            parameters[1].Value = password;

            SqlDataReader reader = SQLProcedureHelper.ExecuteReader(_login, parameters);
            if (reader.HasRows)
            {
                reader.Read();
                user = new UserEntity();
                user.Uid = Convert.ToInt32(reader["Uid"]);
                user.Uname = reader["Uname"].ToString();
                user.NickName = reader["NickName"].ToString();
                user.MobilePhone = reader["MobilePhone"].ToString();
                user.Phone = reader["Phone"].ToString();
                user.QQ = reader["QQ"].ToString();
                user.Ali = reader["Ali"].ToString();
                user.Msn = reader["Msn"].ToString();
                user.Face = reader["Face"].ToString();
                user.LevelId = Convert.ToInt32(reader["LevelId"]);
                user.LevelName = reader["LevelName"].ToString();
            }
            reader.Close();
            return user;
        }
        /// <summary>
        /// 用户注册
        /// </summary>
        /// <param name="_user">关联的用户实体</param>
        /// <returns>用户ID</returns>
        public int RegNewUser(UserEntity user)
        {
            int result = 0;
            SqlParameter[] parameters = 
            {
                new SqlParameter("@Uname",SqlDbType.NVarChar,50){Value = user.Uname},
                new SqlParameter("@Password",SqlDbType.NVarChar,50){Value= user.Password},
                new SqlParameter("@NickName",SqlDbType.NVarChar,50){Value = user.NickName},
                new SqlParameter("@Enabled",SqlDbType.Int){Value = user.Enabled},
                new SqlParameter("@MobilePhone",SqlDbType.NVarChar,20){Value = user.MobilePhone},
                new SqlParameter("@Phone",SqlDbType.NVarChar,20){Value = user.Phone},
                new SqlParameter("@Msn",SqlDbType.NVarChar,50){Value = user.Msn}
            };


            result = Convert.ToInt32(SQLProcedureHelper.ExecuteScalar(_regNewUser, parameters));
            return result;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageCount"></param>
        /// <returns></returns>
        public List<UserEntity> UserList(int pageSize , int pageIndex , ref int pageCount)
        {
            List<UserEntity> list = new List<UserEntity>();
            SqlParameter[] parameters = 
              {
                  new SqlParameter("@PageIndex",SqlDbType.Int){ Value = pageIndex},
                  new SqlParameter("@PageSize",SqlDbType.Int){ Value= pageSize}
              };
            SqlDataReader reader = SQLProcedureHelper.ExecuteReader(_selectList, parameters);
            while (reader.Read())
            {
                list.Add(new UserEntity()
                {
                    Ali = reader["Ali"].ToString() ,
                    MobilePhone = reader["MobilePhone"].ToString() ,
                    Msn = reader["Msn"].ToString() ,
                    NickName = reader["NickName"].ToString() ,
                    Phone = reader["Phone"].ToString() ,
                    QQ = reader["QQ"].ToString() ,
                    RegTime = Convert.ToDateTime(reader["RegTime"]) ,
                    Uid = Convert.ToInt32(reader["Uid"]) ,
                    Uname = reader["Uname"].ToString() ,
                    Enabled = Convert.ToInt32(reader["Enabled"]) ,
                    LevelId = Convert.ToInt32(reader["LevelId"]) ,
                    LevelName = reader["Name"].ToString()
                });
            }
            return list;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public UserEntity UserProfile(int uid = 0, string uname = "", string nickName = "")
        {
            UserEntity userProfile = null;
            SqlParameter[] parameters = 
            {
                new SqlParameter("@Uid",SqlDbType.Int){Value=uid}
            };

            SqlDataReader reader = SQLProcedureHelper.ExecuteReader(_profile, parameters);

            if (reader.HasRows)
            {
                userProfile = new UserEntity();
                reader.Read();
                userProfile.Uid = Convert.ToInt32(reader["Uid"]);
                userProfile.Uname = reader["Uname"].ToString();
                userProfile.NickName = reader["NickName"].ToString();
                userProfile.MobilePhone = reader["MobilePhone"].ToString();
                userProfile.Phone = reader["Phone"].ToString();
                userProfile.QQ = reader["QQ"].ToString();
                userProfile.Ali = reader["Ali"].ToString();
                userProfile.Msn = reader["Msn"].ToString();
                userProfile.Face = reader["Face"].ToString();
                userProfile.LevelId = Convert.ToInt32(reader["LevelId"]);
                userProfile.Enabled = Convert.ToInt32(reader["Enabled"]);
                userProfile.LevelName = reader["LevelName"].ToString();
            }

            reader.Close();

            return userProfile;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="uid"></param>
        public int DeleteUser(int uid)
        {
            return 0;
        }
        public int UpdateUserState(int uid = 0 , int enabled = 0 , int levelId = 0)
        {
            return SQLProcedureHelper.ExecuteNonQuery(
                _chageState
                , new SqlParameter[] {
                    new SqlParameter("@Uid",SqlDbType.Int){Value = uid}
                    , new SqlParameter("@Enabled",SqlDbType.Int){Value = enabled}
                    , new SqlParameter("@LevelId",SqlDbType.Int){Value = levelId}
                });
        }

    }
}
